package userConsole.jdbc;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import userConsole.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

/**
 * @author Godv
 * @version 1.0
 */
public class UserJDBC {

    public int checkUsername(Connection connection, String username) throws SQLException {
        String sql = "select count(id) from user where username=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }

    public void registerUser(Connection connection, String username, String password) throws SQLException {
        String sql = "insert into user(username, password) values (?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    public User loginUser(Connection connection, String username) throws SQLException {
        String sql = "select id, username, password from user where username=? limit 1";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        if(resultSet.next()) {
            user = new User();
            user.setUsername(username);
            user.setPassword(resultSet.getString("password"));
            user.setId(resultSet.getInt("id"));
        }
        resultSet.close();
        preparedStatement.close();
        return user;
    }

    public void insertUser(Connection connection, String username, String password, String age) throws SQLException {
        String sql = "insert into user(username, password, age) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        preparedStatement.setString(3, age);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    public ArrayList<JSONObject> userList(Connection connection) throws SQLException {
        String sql = "select id, username, password, age from user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        while(resultSet.next()) {
            user = new User();
            int id = resultSet.getInt("id");
            user.setId(id);
            String username = resultSet.getString("username");
            user.setUsername(username);
            String password = resultSet.getString("password");
            user.setPassword(password);
            int age = resultSet.getInt("age");
            user.setAge(age);
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }

    public User selectUserById(Connection connection, String id) throws SQLException {
        String sql = "select id, username, password, age from user where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        if(resultSet.next()) {
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setAge(resultSet.getInt("age"));
        }
        return user;
    }
    public ArrayList<JSONObject> selectUserByName(Connection connection, String username) throws SQLException {
        String sql = "select id,username,password,age from user where username like ?";
        System.out.println(sql);
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+username+"%");
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        ArrayList<JSONObject> list = new ArrayList<>();
        while(resultSet.next())
        {
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setAge(resultSet.getInt("age"));
            list.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return list;
    }

    public void updateById(Connection connection, String id, String username, String password, String age) throws SQLException {
        String sql = "update user set username=?, password=?, age=? where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        preparedStatement.setString(3, age);
        preparedStatement.setString(4, id);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    public void deleteById(Connection connection, String id) throws SQLException {
        String sql = "delete from user where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, id);
        preparedStatement.execute();
        connection.commit();
        preparedStatement.close();
    }

    public int selectCount(Connection connection) throws SQLException {
        String sql = "select count(id) from user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }
    public int selectCountForName(Connection connection,String username) throws SQLException {
        String sql = "select count(id) from user where username like ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+username+"%");
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }
    public int selectCountForPassword(Connection connection,String password) throws SQLException {
        String sql = "select count(id) from user where password like ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+password+"%");
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }
    public int selectCountForNameOrPassword(Connection connection,String username,String password) throws SQLException {
        String sql = "select count(id) from user where username like ? or password like ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+username+"%");
        preparedStatement.setString(2,"%"+password+"%");
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count(id)");
        resultSet.close();
        preparedStatement.close();
        return count;
    }


    public ArrayList<JSONObject> page(Connection connection, int startIndex, int showCount) throws SQLException {
        String sql = "select id, username, password, age from user limit ?,?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, startIndex);
        preparedStatement.setInt(2, showCount);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        while(resultSet.next()) {
            user = new User();
            int id = resultSet.getInt("id");
            user.setId(id);
            String username = resultSet.getString("username");
            user.setUsername(username);
            String password = resultSet.getString("password");
            user.setPassword(password);
            int age = resultSet.getInt("age");
            user.setAge(age);
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }
    public ArrayList<JSONObject> pageForName(Connection connection,String username,int startIndex,int showCount) throws SQLException {
        String sql = "select id, username, password, age from user where username like ? limit ?,?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+username+"%");
        preparedStatement.setInt(2,startIndex);
        preparedStatement.setInt(3,showCount);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        while(resultSet.next()) {
            user = new User();
            int id = resultSet.getInt("id");
            user.setId(id);
            String name = resultSet.getString("username");
            System.out.println(name);
            user.setUsername(name);
            String password = resultSet.getString("password");
            user.setPassword(password);
            int age = resultSet.getInt("age");
            user.setAge(age);
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }
    public ArrayList<JSONObject> pageForPassword(Connection connection,String password,int startIndex,int showCount) throws SQLException {
        String sql = "select id, username, password, age from user where password like ? limit ?,?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+password+"%");
        preparedStatement.setInt(2,startIndex);
        preparedStatement.setInt(3,showCount);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        while(resultSet.next()) {
            user = new User();
            int id = resultSet.getInt("id");
            user.setId(id);
            String name = resultSet.getString("username");
            System.out.println(name);
            user.setUsername(name);
            String mypassword = resultSet.getString("password");
            user.setPassword(mypassword);
            int age = resultSet.getInt("age");
            user.setAge(age);
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }
    public ArrayList<JSONObject> pageForNameOrPassword(Connection connection,String username,String password,int startIndex,int showCount) throws SQLException {
        String sql = "select id, username, password, age from user where username like ? or user.password like ? limit ?,?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"%"+username+"%");
        preparedStatement.setString(2,"%"+password+"%");
        preparedStatement.setInt(3,startIndex);
        preparedStatement.setInt(4,showCount);
        ResultSet resultSet = preparedStatement.executeQuery();
        User user = null;
        ArrayList<JSONObject> arrayList = new ArrayList<>();
        while(resultSet.next()) {
            user = new User();
            int id = resultSet.getInt("id");
            user.setId(id);
            String name = resultSet.getString("username");
            System.out.println(name);
            user.setUsername(name);
            String mypassword = resultSet.getString("password");
            user.setPassword(mypassword);
            int age = resultSet.getInt("age");
            user.setAge(age);
            arrayList.add(new JSONObject(user));
        }
        resultSet.close();
        preparedStatement.close();
        return arrayList;
    }


    /*
    分页操作
    1. 我们需要知道总计有多少条记录(整个表中)     count代表总计
    2. 我们需要知道每页显示多少条记录(页面上)     showCount代表每页显示
    3. 然后根据固定的算法，计算出总计有多少页     page
    例如:
         总计 40条   每页显示 5条    总计 8页
         总计 41条   每页显示 5条    总计 9页
         总计 39条   每页显示 5条    总计 8页
         总计 36条   每页显示 5条    总计 8页
         总计 35条   每页显示 5条    总计 7页
         计算公式：总计条数-1 除以  每页显示条数  加1  得到最终的总计页数
         page = (count - 1)/showCount + 1;
     */

    public static void main(String[] args) throws JSONException {
        JSONObject jsonObject = new JSONObject();

//        HashMap<String, String> hashMap = new HashMap<>();
//        hashMap.put("username", "gxa");
//        hashMap.put("password", "123456");
//        jsonObject.put("map", hashMap);
//        System.out.println(jsonObject);
//
//        JSONObject innerJSONObject = jsonObject.getJSONObject("map");
//        System.out.println(innerJSONObject);
//        System.out.println(innerJSONObject.getString("username"));
//        System.out.println(innerJSONObject.getString("password"));

//        HashSet<String> set = new HashSet<>();
//        set.add("a");
//        set.add("b");
//        set.add("c");
//        jsonObject.put("set", set);
//        System.out.println(jsonObject);
//        JSONArray jsonArray = jsonObject.getJSONArray("set");
//        System.out.println(jsonArray);
//        System.out.println(jsonArray.getString(0));

//        ArrayList<String> list = new ArrayList<>();
//        list.add("a");
//        list.add("b");
//        list.add("c");
//        jsonObject.put("list", list);
//        System.out.println(jsonObject);
//        JSONArray jsonArray = jsonObject.getJSONArray("list");
//        System.out.println(jsonArray);
//        System.out.println(jsonArray.getString(0));

//        User user = new User();
//        user.setUsername("gxa");
//        user.setPassword("123456");
//        jsonObject.put("user", new JSONObject(user));
//        System.out.println(jsonObject);
//        JSONObject innerJSONObject = jsonObject.getJSONObject("user");
//        System.out.println(innerJSONObject);
//        System.out.println(innerJSONObject.getString("username"));
//        System.out.println(innerJSONObject.getString("password"));


        ArrayList<JSONObject> list = new ArrayList<>();
        User user = new User();
        user.setUsername("gxa");
        user.setPassword("123456");
        list.add(new JSONObject(user));

        user = new User();
        user.setUsername("think");
        user.setPassword("654321");
        list.add(new JSONObject(user));

        jsonObject.put("list", list);
        System.out.println(jsonObject);

        JSONArray jsonArray = jsonObject.getJSONArray("list");
        for(int i = 0; i < jsonArray.length(); i++) {
            JSONObject innerJSONObject = jsonArray.getJSONObject(i);
            System.out.println(innerJSONObject.getString("username"));
            System.out.println(innerJSONObject.getString("password"));
        }
        /*
        java中普通的对象，在json中都是JSONObject
        map的对象，在json中都是JSONObject
        set的对象，在json中都是JSONArray
        list的对象，在json中都是JSONArray
         */


    }
}
